package com.example.demo.service.impl;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.Iterator;


public class ExcelToCSVServiceImpl {
    /**
     * 将Excel文件转换为CSV文件。
     *
     * @param excelFilePath Excel文件路径
     * @param csvOutputPath CSV输出文件路径
     */
    public static String convertExcelToCsv(String excelFilePath, String csvOutputPath) {
        // 这个方法存在一个问题：会将整数转化为小数，不过对ai来说没啥区别
        String csv = "";
        if (csvOutputPath == null) csvOutputPath = "src/main/java/com/example/demo/service/impl/testDir/test.csv";
        try (FileInputStream fis = new FileInputStream(new File(excelFilePath));
             // 保存到csv文件，方便测试，但是可能存在并发抢写入问题？
             // todo 后续完善可能的并发问题
             Workbook workbook = new XSSFWorkbook(fis);
             FileWriter writer = new FileWriter(csvOutputPath)) {

            // 遍历工作簿中的每个工作表
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                Iterator<Row> rowIterator = sheet.iterator();

                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    boolean firstCell = true;

                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        String data = getCellDataAsString(cell);

                        if (!firstCell) {
                            writer.append(",");
                            csv = csv + ",";
                        } else {
                            firstCell = false;
                        }
                        writer.append(data);
                        csv = csv + data;
                    }
                    csv = csv + "\n";
                    writer.append("\n");
                }
            }

            System.out.println("Excel to CSV conversion completed successfully.");

        } catch (IOException e) {
            System.out.println("Error converting Excel to CSV: " + e.getMessage());
            e.printStackTrace();
        }
        return csv;
    }

    /**
     * 将单元格数据转换为字符串。
     *
     * @param cell 单元格对象
     * @return 单元格数据的字符串表示形式
     */
    private static String getCellDataAsString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case BLANK:
                return "";
            default:
                return ""; // 处理其他类型或未知类型的情况
        }
    }

    public static void main(String[] args) {
        // 用于测试的Main函数
        String excelFilePath = "src/main/java/com/example/demo/service/impl/testDir/test.xlsx"; // Excel文件路径
        String csvOutputPath = "src/main/java/com/example/demo/service/impl/testDir/test.csv"; // 输出CSV文件路径

        System.out.println(convertExcelToCsv(excelFilePath, csvOutputPath));
    }
}
